set spark.sql.crossJoin.enabled=true;
with retention_summary as (
    select final_sign_network_code                       --最终签收派件网点编码
         , end_agent_code                                --末端网点所属代理区编码
         , final_sign_network_name                       --最终签收派件网点编码
         , end_agent_name                                --末端网点所属代理区名称
         , end_provider_id                               --末端网点所属省份id
         , end_provider_name                             --末端网点所属省份名称
         , end_city_id                                   --末端网点所属城市id
         , end_city_name                                 --末端网点所属城市名称
         , is_contain_difficult
         , sum(need_sign_count_1d) as need_sign_count_1d --1天应签收
         , sum(retention_count_1d) as retention_count_1d --1天内滞留
         , sum(need_sign_count_2d) as need_sign_count_2d --2天应签收
         , sum(retention_count_2d) as retention_count_2d --2天内滞留
         , sum(need_sign_count_3d) as need_sign_count_3d --3天应签收
         , sum(retention_count_3d) as retention_count_3d --3天内滞留
         , sum(need_sign_count_5d) as need_sign_count_5d --5天应签收
         , sum(retention_count_5d) as retention_count_5d --5天内滞留
         , sum(need_sign_count_7d) as need_sign_count_7d --7天应签收
         , sum(retention_count_7d) as retention_count_7d --7天内滞留
         , final_plan_sign_date                          --规划签收日期
    from jms_dm.dm_network_retention_summary_dt
    where dt = '{{ execution_date | cst_ds }}'
--       and is_contain_difficult = 2
    group by final_sign_network_code --最终签收派件网点编码
           , end_agent_code          --末端网点所属代理区编码
           , final_sign_network_name --最终签收派件网点编码
           , end_agent_name          --末端网点所属代理区名称
           , final_plan_sign_date
           , end_provider_id         --末端网点所属省份id
           , end_provider_name       --末端网点所属省份名称
           , end_city_id             --末端网点所属城市id
           , end_city_name           --末端网点所属城市名称
           , is_contain_difficult
),
     taking_union as (
         select network_code
              , network_name
              , sum(scan_sum) as scan_sum
              , type1
              , date_time
         from jms_dm.dm_network_taking_union_sign_hour_sum_dt
         where dt = '{{ execution_date | cst_ds }}'
           and type1 = '时效签收量'
         group by network_code
                , network_name
                , type1
                , date_time
     ),
     warm_config_before as (
         select datetime
              , max(1pressure) as 1pressure
              , max(3pressure) as 3pressure
              , max(5pressure) as 5pressure
              , max(7pressure) as 7pressure
              , max(1frame)    as 1frame
              , max(3frame)    as 3frame
              , max(5frame)    as 5frame
              , max(7frame)    as 7frame
         from (
                  select datetime
                       , if(time_frame = '1日内', pressure, 0)    as 1pressure --压力值
                       , if(time_frame = '3日内', pressure, 0)    as 3pressure
                       , if(time_frame = '5日内', pressure, 0)    as 5pressure
                       , if(time_frame = '7日内', pressure, 0)    as 7pressure
                       , if(time_frame = '1日内', count_frame, 0) as 1frame    --票量
                       , if(time_frame = '3日内', count_frame, 0) as 3frame
                       , if(time_frame = '5日内', count_frame, 0) as 5frame
                       , if(time_frame = '7日内', count_frame, 0) as 7frame
                  from (
                           select time_frame
                                , pressure
                                , count_frame
                                , effective_start_time
                                , effective_end_time
                                , '{{ execution_date | cst_ds }}' as datetime
                           from jms_dim.dim_tab_warehouse_warm_config_base
                           where delete_flag = 1
                             and substr(effective_start_time, 1, 10) <= '{{ execution_date | cst_ds }}'
                             and substr(effective_end_time, 1, 10) >= '{{ execution_date | cst_ds }}'
                       ) tmp
              ) t
         group by datetime
     )
insert
overwrite
table
jms_dm.dm_mng_explosion_warehouse_network_mid_dt
partition
(
dt
)
select t1.final_sign_network_code                                                                                                  --最终签收派件网点编码
     , t1.final_sign_network_name                                                                                                  --最终签收派件网点编码
     , t1.end_agent_code                                                                                                           --末端网点所属代理区编码
     , t1.end_agent_name                                                                                                           --末端网点所属代理区名称
     , t1.need_sign_count_1d                                                                                                       --1天应签收
     , t1.retention_count_1d                                                                                                       --1天内滞留
     , t1.need_sign_count_2d                                                                                                       --2天应签收
     , t1.retention_count_2d                                                                                                       --2天内滞留
     , t1.need_sign_count_3d                                                                                                       --3天应签收
     , t1.retention_count_3d                                                                                                       --3天内滞留
     , t1.need_sign_count_5d                                                                                                       --5天应签收
     , t1.retention_count_5d                                                                                                       --5天内滞留
     , t1.need_sign_count_7d                                                                                                       --7天应签收
     , t1.retention_count_7d                                                                                                       --7天内滞留
     , t2.scan_sum                                                                                                                 --时效签收量
     , case
           when (t1.retention_count_1d / nvl(t2.scan_sum, 1) >= t3.1pressure and retention_count_1d >= t3.1frame) or
                (t1.retention_count_3d / nvl(t2.scan_sum, 1) >= t3.3pressure and retention_count_3d >= t3.3frame)
               then 1
           else 0 end                                                                                            as yali_flag      --1代表压力值满足的要保留,0非代表不满足任何一种情况
     , t1.final_plan_sign_date                                                                                                     --规划签收日期
     , t1.end_provider_id                                                                                                          --末端网点所属省份id
     , t1.end_provider_name                                                                                                        --末端网点所属省份名称
     , t1.end_city_id                                                                                                              --末端网点所属城市id
     , t1.end_city_name                                                                                                            --末端网点所属城市名称
     , t4.zone_code                                                                                              as district_code
     , t4.zone_name                                                                                              as district_desc
     , t4.zone_id                                                                                                as district_id
     , t4.area_code                                                                                              as area_code
     , t4.area_name                                                                                              as area_name
     , t1.is_contain_difficult
     , t3.1pressure                                                                                                                --1日内压力值
     , t3.3pressure                                                                                                                --3日内压力值
     , t3.5pressure                                                                                                                --5日内压力值
     , t3.7pressure                                                                                                                --7日内压力值
     , t3.1frame                                                                                                                   --1日内票量
     , t3.3frame                                                                                                                   --3日内票量
     , t3.5frame                                                                                                                   --5日内票量
     , t3.7frame                                                                                                                   --7日内票量
     , if(t1.retention_count_1d / nvl(t2.scan_sum, 1) >= t3.1pressure and retention_count_1d >= t3.1frame, 1,
          0)                                                                                                     as one_pre_flag   --1日判断1是0否
     , if(t1.retention_count_3d / nvl(t2.scan_sum, 1) >= t3.3pressure and retention_count_3d >= t3.3frame, 1,
          0)                                                                                                     as three_pre_flag --3日判断1是0否
     , '{{ execution_date | cst_ds }}'                                                                           as dt
from (
         select *
         from retention_summary
     ) t1
         left join
     (
         select *
         from taking_union
     ) t2
     on t1.final_sign_network_code = t2.network_code
         and t1.final_plan_sign_date = t2.date_time
         left join warm_config_before t3
                   on t1.final_plan_sign_date = t3.datetime
         left join jms_dim.dim_network_whole_massage t4
                   on t1.final_sign_network_code = t4.code
    distribute by dt
;